package JurisPro.modelo.banco;

import java.sql.Date;
import java.sql.Timestamp;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import JurisPro.modelo.Processo;

public class ProcessosDAO {

	public static ProcessosDAO singleton = null;
	
	public static ProcessosDAO getInstance() {
		if(singleton == null)
			singleton = new ProcessosDAO();
		return ProcessosDAO.singleton;
	}

	
	public int insertProcess(Processo processo) {
				
		String sql = "INSERT INTO Processo (numprocesso, pasta, dataEntrada, autor, parte, cliente, foro, pdf, localidade, IDResponsavel) " +
				"VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
		PreparedStatement state = Conexao.getInstance().getPrepState(sql);
		int rows = 0;
		try {
			state.setString(1, processo.getNumProcesso());
			state.setInt(2, processo.getPasta());
			state.setTimestamp(3, new Timestamp(processo.getDataEntrada().getTime()));
			//SimpleDateFormat fmt = new SimpleDateFormat("dd/MM/yyyy");
			//state.setString(3, fmt.format(processo.getDataEntrada()));
			state.setString(4, processo.getAutor());
			state.setString(5, processo.getParte());
			state.setBoolean(6, processo.getCliente());
			state.setString(7, processo.getForo());
			state.setBytes(8, processo.getPdf());
			state.setString(9, processo.getLocal());
			state.setInt(10, processo.getResponsavel());
			rows = state.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rows;	
	}
	
	public int updateProcess(Processo processo) {
				
		String sql = "UPDATE Processo SET pasta = ?, dataEntrada = ?, autor = ?, parte = ?, cliente = ?,"+
				" foro = ?, localidade = ?, IDResponsavel = ? WHERE numprocesso = ?;";
		PreparedStatement state = Conexao.getInstance().getPrepState(sql);
		
		int rows = 0;
		try {
			state.setInt(1, processo.getPasta());
			state.setDate(2, new Date(processo.getDataEntrada().getTime()));
			state.setString(3, processo.getAutor());
			state.setString(4, processo.getParte());
			state.setBoolean(5, processo.getCliente());
			state.setString(6, processo.getForo());
			state.setString(7, processo.getLocal());
			state.setInt(8, processo.getResponsavel());
			state.setString(9, processo.getNumProcesso());		
			rows = state.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rows;			
	}
	
	
	public Processo selectProcessByNumber(String numProcesso) {
		String sql = "SELECT * FROM Processo where numProcesso = ?;";
		Processo processo = null;
		PreparedStatement state = Conexao.getInstance().getPrepState(sql);
				
		try {		
			state.setString(1, numProcesso);
			ResultSet rs = state.executeQuery();
			
			while (rs.next()) {
				processo = new Processo(rs.getInt("pasta"), rs.getDate("dataEntrada"), numProcesso, 
						rs.getString("autor"), rs.getString("parte"), rs.getBoolean("cliente"), 
						rs.getString("localidade"), rs.getString("foro"), rs.getInt("IDResponsavel"), rs.getBytes("pdf"));
						processo.setNomeResponsavel(this.selectNameLawyer(processo.getResponsavel()));
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return processo;
	}
	
	
	public String selectNameLawyer(int id){
		String sql = "SELECT nome FROM Usuario where IDUsuario = ?;";
		PreparedStatement state = Conexao.getInstance().getPrepState(sql);
		String usuario = "";
		try {
			state.setInt(1, id);
			ResultSet rs = state.executeQuery();
			while (rs.next()) {
				 usuario = rs.getString("nome");
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return usuario;
		
	}
	
	public ArrayList<Processo> selectProcessByLawyer(int id) {
		String sql = "SELECT * FROM Processo where IDResponsavel = ?;";
		Processo processo;
		ArrayList<Processo> processos = new ArrayList<Processo>();
		PreparedStatement state = Conexao.getInstance().getPrepState(sql);
		
		try {
			state.setInt(1, id);
			ResultSet rs = state.executeQuery();
			while (rs.next()) {
				processo = new Processo(rs.getInt("pasta"), rs.getDate("dataEntrada"), rs.getString("numProcesso"), 
						rs.getString("autor"), rs.getString("parte"), rs.getBoolean("cliente"), 
						rs.getString("localidade"), rs.getString("foro"), rs.getInt("IDResponsavel"), rs.getBytes("pdf"));
				processo.setNomeResponsavel(this.selectNameLawyer(processo.getResponsavel()));
				processos.add(processo);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return processos;
	}

	public ArrayList<Processo> selectProcessByAuthor(String autor) {
		String sql = "SELECT * FROM Processo where autor like ?;";
		Processo processo;
		ArrayList<Processo> processos = new ArrayList<Processo>();
		PreparedStatement state = Conexao.getInstance().getPrepState(sql);
		
		try {		
			state.setString(1, autor+"%");
			ResultSet rs = state.executeQuery();
			while (rs.next()) {
				processo = new Processo(rs.getInt("pasta"), rs.getDate("dataEntrada"), rs.getString("numProcesso"), 
						rs.getString("autor"), rs.getString("parte"), rs.getBoolean("cliente"), 
						rs.getString("localidade"), rs.getString("foro"), rs.getInt("IDResponsavel"), rs.getBytes("pdf"));
				processo.setNomeResponsavel(this.selectNameLawyer(processo.getResponsavel()));
				processos.add(processo);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return processos;
	}
	
	public ArrayList<Processo> selectProcessByParte(String parte) {
		String sql = "SELECT * FROM Processo where parte like ?;";
		Processo processo;
		ArrayList<Processo> processos = new ArrayList<Processo>();
		PreparedStatement state = Conexao.getInstance().getPrepState(sql);
		
		try {		
			state.setString(1, parte+"%");
			ResultSet rs = state.executeQuery();
			while (rs.next()) {
				processo = new Processo(rs.getInt("pasta"), rs.getDate("dataEntrada"), rs.getString("numProcesso"), 
						rs.getString("autor"), rs.getString("parte"), rs.getBoolean("cliente"), 
						rs.getString("localidade"), rs.getString("foro"), rs.getInt("IDResponsavel"), rs.getBytes("pdf"));
				processo.setNomeResponsavel(this.selectNameLawyer(processo.getResponsavel()));
				processos.add(processo);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return processos;
	}
	
	
	public ArrayList<Processo> selectProcessAll() {
		String sql = "SELECT * FROM Processo;";
		Processo processo;
		ArrayList<Processo> processos = new ArrayList<Processo>();
		ResultSet rs = Conexao.getInstance().selectComand(sql);
		try {
			while (rs.next()) {
				processo = new Processo(rs.getInt("pasta"), rs.getDate("dataEntrada"), rs.getString("numProcesso"), 
						rs.getString("autor"), rs.getString("parte"), rs.getBoolean("cliente"), 
						rs.getString("localidade"), rs.getString("foro"), rs.getInt("IDResponsavel"), rs.getBytes("pdf"));
				processo.setNomeResponsavel(this.selectNameLawyer(processo.getResponsavel()));
				processos.add(processo);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return processos;
	}
	
	
}

